from sqlalchemy import Column, Integer, String, text
from sqlalchemy.exc import PendingRollbackError
from sqlalchemy.ext.declarative import declarative_base
from inits import DB, dbSession

#定义基类
Base = declarative_base()

class sk_shop_phone_code(Base):
    __tablename__ = 'sk_shop_phone_code'  # 数据库中表的名字
    id = Column(Integer, primary_key=True)
    shop_id = Column(Integer)
    user_id = Column(Integer)
    send_time = Column(Integer)
    code = Column(String(6))
    submit_time = Column(Integer)
    status = Column(Integer)
    expire_time = Column(Integer)

    # 插入数据
    def insert_phone_code(self, phone_code):
        try:
            dbSession.add(phone_code)

            # 将会话的改动发送到数据库
            dbSession.flush()
            id = phone_code.id
            # 提交到数据库
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            # 关闭Session
            dbSession.close()
        return id

    # 根据ID获取数据
    def get_phone_code_by_id(self, id):
        try:
            sql = text("""
                SELECT id, code FROM sk_shop_phone_code WHERE id=:id
            """)
            res = dbSession.execute(sql, {"id": id}).one()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()
        return res

    # 修改验证码的状态
    def update_shop_code_status(self, code_id, status):
        try:
            sql = text("""
                  UPDATE sk_shop_phone_code SET status =:status WHERE id=:code_id
              """)
            dbSession.execute(sql, {"status": status, "code_id": code_id})
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()